/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;
import model.entities.Course;
import model.entities.Teacher;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.CourseDAOService;
/**
 *
 * @author K u Ku
 */
public class CourseDAO implements CourseDAOService {
    private static CourseDAO courseDAO;
    
    public static CourseDAO getInstance(){
        if(courseDAO ==null){
            courseDAO = new CourseDAO();
        }
        return courseDAO;
    }
    
    // Function get all Course
    @Override
    public List<model.entities.Course> getCourseList(){
        ArrayList<model.entities.Course> lstCourse = new ArrayList<model.entities.Course>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse WHERE IsActived='true'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course c = new Course();
                c.setCourseID(rs.getInt("CourseID"));
                c.setName(rs.getString("Name"));
                c.setFees(rs.getDouble("Fees"));
                c.setDuration(rs.getFloat("Duration"));
                c.setStartDate(rs.getDate("StartDate"));
                c.setFinishDate(rs.getDate("FinishDate")); 
                c.setNotes(rs.getString("Notes"));
                Teacher teacher = TeacherDAO.getInstance().getTeacherByID(rs.getInt("TeacherID"));
                c.setTeacher(teacher);
//                teacher.getListCourse().add(c);
                lstCourse.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstCourse;
    }
    
    //Function get Course by ID
    @Override
    public Course getCourseByID(int CourseID){
        Course c = new Course();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse WHERE CourseID= ? and IsActived='true'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, CourseID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                c.setCourseID(rs.getInt("CourseID"));
                c.setName(rs.getString("Name"));
                c.setFees(rs.getDouble("Fees"));
                c.setDuration(rs.getFloat("Duration"));
                c.setStartDate(rs.getDate("StartDate"));
                c.setFinishDate(rs.getDate("FinishDate"));
                c.setNotes(rs.getString("Notes"));
                Teacher teacher = TeacherDAO.getInstance().getTeacherByID(rs.getInt("TeacherID"));
                c.setTeacher(teacher);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return c;
    }
    
    @Override
    public boolean insertCourse(model.entities.Course course){
        String query = "INSERT INTO tblCourse VALUES(?,?,?,?,?,?,?,?)";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, course.getName());
            pstmt.setDouble(2, course.getFees());
            pstmt.setDate(3,(java.sql.Date) course.getStartDate());
            pstmt.setDate(4,(java.sql.Date) course.getFinishDate());
            pstmt.setFloat(5, course.getDuration());
            pstmt.setString(6, course.getNotes());
            pstmt.setInt(7, course.getTeacher().getTeacherID());
            pstmt.setBoolean(8, true);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    @Override
    public boolean updateCourse(model.entities.Course course){
        String query = "UPDATE tblCourse SET Name=?,Fees=?,StartDate=?,FinishDate=?,Duration=?,Notes=?,TeacherID=? WHERE CourseID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, course.getName());
            pstmt.setDouble(2, course.getFees());
            pstmt.setDate(3,(java.sql.Date) course.getStartDate());
            pstmt.setDate(4,(java.sql.Date) course.getFinishDate());
            pstmt.setFloat(5, course.getDuration());
            pstmt.setString(6, course.getNotes());
            pstmt.setInt(7, course.getTeacher().getTeacherID());
            pstmt.setInt(8, course.getCourseID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    @Override
    public boolean deleteCourse(int courseID){
        String query = "UPDATE tblCourse SET IsActived='false' WHERE CourseID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, courseID);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } 
    }

    public List<Course> findByName(String name) {
        List<Course> lstCourse = new ArrayList<Course>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse WHERE IsActived='true' AND Name= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course c = new Course();
                c.setCourseID(rs.getInt("CourseID"));
                c.setDuration(rs.getFloat("Duration"));
                c.setFinishDate(rs.getDate("FinishDate"));
                c.setIsActived(true);
                c.setName(rs.getString("Name"));
                c.setFees(rs.getDouble("Fees"));
                c.setNotes(rs.getString("Notes"));
                c.setStartDate(rs.getDate("StartDate"));
                Teacher t = TeacherDAO.getInstance().getTeacherByID(rs.getInt("TeacherID"));
                c.setTeacher(t);
                lstCourse.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstCourse;
    }

    @Override
    public List<Course> findByID(int id) {
        List<Course> lstCourse = new ArrayList<Course>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse WHERE IsActived='true' AND CourseID= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course c = new Course();
                c.setCourseID(rs.getInt("CourseID"));
                c.setDuration(rs.getFloat("Duration"));
                c.setFinishDate(rs.getDate("FinishDate"));
                c.setIsActived(true);
                c.setName(rs.getString("Name"));
                c.setFees(rs.getDouble("Fees"));
                c.setNotes(rs.getString("Notes"));
                c.setStartDate(rs.getDate("StartDate"));
                Teacher t = TeacherDAO.getInstance().getTeacherByID(rs.getInt("TeacherID"));
                c.setTeacher(t);
                lstCourse.add(c);
            }
            } catch (SQLException ex) {
                Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
    return lstCourse;
    }

    @Override
    public List<Course> getCourseByTeacherID(int teacherID) {
        List<Course> lstCourse = new ArrayList<Course>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse WHERE IsActived='true' AND TeacherID= ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, teacherID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course c = new Course();
                c.setCourseID(rs.getInt("CourseID"));
                c.setDuration(rs.getFloat("Duration"));
                c.setFinishDate(rs.getDate("FinishDate"));
                c.setIsActived(true);
                c.setName(rs.getString("Name"));
                c.setFees(rs.getDouble("Fees"));
                c.setNotes(rs.getString("Notes"));
                c.setStartDate(rs.getDate("StartDate"));
                lstCourse.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CourseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstCourse;
    }
}
